package com.newbsd.web.maventest.dao.impl;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.jar.Attributes.Name;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.newbsd.web.maventest.dao.ConnectDb;
import com.newbsd.web.maventest.dao.UserDao;
import com.newbsd.web.maventest.pojo.Ticket;
import com.newbsd.web.maventest.pojo.User;

/**
 * @author Mei
 *
 */
public class UserDaoImpl implements UserDao{

	
	//用户登录
	public User findByUsername(String username) {
		
		User user  = null;
		Connection conn = ConnectDb.Connect();
		
		if (null !=null) {
			ResultSet rs = null;
			try {
				PreparedStatement pstmt = conn.prepareStatement("select * from user where username=?");
				pstmt.setString(1, username);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					user = new User();
					user.setUserid(rs.getInt("userid"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setRealname(rs.getString("realname"));
					user.setSex(rs.getString("sex"));
					user.setCity(rs.getString("city"));
					user.setProvince(rs.getString("province"));
					user.setCertType(rs.getString("certType"));
					user.setCert(rs.getString("cert"));
			
					user.setUserType(rs.getString("userType"));
					user.setContent(rs.getString("content"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return user;
		
	}
	//查询所有用户信息
	public List<User> getAll() {
		List<User> users = new ArrayList<User>();
		Connection conn = ConnectDb.Connect();
		//执行事务
		if (null != conn ) {
			ResultSet rs = null;
			try {
				//Statement st = conn.createStatement();
				PreparedStatement pstmt = conn.prepareStatement("select * from user ");
				rs = pstmt.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			if (null != rs) {
				User user = null;
				try {
					
					while (rs.next()) {
						user = new User();
						user.setUserid(rs.getInt("userid"));
						user.setUsername(rs.getString("username"));
						user.setPassword(rs.getString("password"));
						user.setRealname(rs.getString("realname"));
						user.setSex(rs.getString("sex"));
						user.setCity(rs.getString("city"));
						user.setProvince(rs.getString("province"));
						user.setCertType(rs.getString("certType"));
						user.setCert(rs.getString("cert"));
						
						user.setUserType(rs.getString("userType"));
						user.setContent(rs.getString("content"));
			
						users.add(user);
						
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return users;
	}
	//分页查询所有用户信息
	public List<User> getAll(int pagesize,int rows) {
		List<User> users = new ArrayList<User>();
		Connection conn = ConnectDb.Connect();
		//执行事务
		if (null != conn ) {
			ResultSet rs = null;
			try {
				PreparedStatement pstmt = conn.prepareStatement("select * from user limit ?,?");
				pstmt.setInt(1, (pagesize-1)*rows);
				pstmt.setInt(2, rows);
				
				rs = pstmt.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			if (null != rs) {
				User user = null;
				try {
					
					while (rs.next()) {
						user = new User();
						user.setUserid(rs.getInt("userid"));
						user.setUsername(rs.getString("username"));
						user.setPassword(rs.getString("password"));
						user.setRealname(rs.getString("realname"));
						user.setSex(rs.getString("sex"));
						user.setCity(rs.getString("city"));
						user.setProvince(rs.getString("province"));
						user.setCertType(rs.getString("certType"));
						user.setCert(rs.getString("cert"));
						
						user.setUserType(rs.getString("userType"));
						user.setContent(rs.getString("content"));
			
						users.add(user);
						
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return users;
	}

	//新增用户
	public void addUser(User user) {
		Connection conn = ConnectDb.Connect();
		
		if (null != conn ) {
			try {
				PreparedStatement pstmt = conn.prepareStatement("insert into user(username,password,realname,sex,city,province,certType,cert,userType,content) values(?,?,?,?,?,?,?,?,?,?)");
				
				pstmt.setString(1, user.getUsername());
				pstmt.setInt(2,Integer.parseInt(user.getPassword()));
				pstmt.setString(3, user.getRealname());
				pstmt.setString(4, user.getSex());
				pstmt.setString(5, user.getCity());
				pstmt.setString(6, user.getProvince());
				pstmt.setString(7, user.getCertType());
				System.out.println(user.getCertType());
				pstmt.setString(8, user.getCert());
				
			   
				pstmt.setString(9, user.getUserType());
				pstmt.setString(10, user.getContent());
				
				pstmt.execute();
				
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("增加用户信息失败!");
			}
		}
	}
	
	//获取用户总数
	@Override
	public int countUser() {
		int total = 0;
		Connection conn = ConnectDb.Connect();
		
		if (null != conn ) {
			ResultSet rs = null;
			try {
				Statement st =conn.createStatement();
				rs = st.executeQuery("select count(*) count from user ");
				while (rs.next()) {
					total = rs.getInt("count");
				}
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("增加用户信息失败!");
			}
		}
		return total;
		
	}
	
	//删除用户
	@Override
	public void delUser(int id) {
	
		Connection conn = ConnectDb.Connect();
		if (null != conn) {
			try {
				
				PreparedStatement pst = conn.prepareStatement("delete from user where id=?");
				pst.setInt(1, id);
				
				pst.executeUpdate();
			
			} catch (SQLException e) {
				// TODO: handle exception
			}
		}
		
	}
	
	
	
	
	//导出用户信息到excel
	
	
	
	
	
	
	
}
